package com.jse.jdbc;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.function.Supplier;

import com.jse.Casts;
import com.jse.Io;
import com.jse.Lang;
import com.jse.Strings;
import com.jse.Times;
import com.jse.json.Json;
import com.jse.json.JsonArray;
import com.jse.json.JsonObject;

public class Sql {
	
	private StringBuilder sql;//完整sql
	private String type ="";//insert update delete select
	private String table ="";
	private String pk ="id";
	private long auto;
	private boolean incr;//自增
	private List<String> fields=new ArrayList<String>();//null 则 *
	private List<Object> params=new ArrayList<Object>();//jdbc参数
	private ArrayList<List<Object>> wheres=new ArrayList<>();
	private String orderBy;//order by
	private boolean desc;//desc
	private String groupBy;//group by
	private boolean distinct;
	private boolean isQuerylist;
	private List<Object> querylist;
	private Set<Object> querySet;
	private Object single;
	private Class<?> c;
	private JsonObject obj;
	
	private int pageNumber=-1;//分页 limit 0,1 | offset 10
	private int pageSize=-1;//分页 limit 0,1 | offset 10
	
	public Sql() {
		
	}
	public Sql(String sql) {
		String x=sql.toLowerCase().trim();
		if(x.startsWith("select")) {
			this.type="select";
		}else if(x.startsWith("insert")) {
			this.type="insert";
		}else if(x.startsWith("update")) {
			this.type="update";
		}else if(x.startsWith("delete")) {
			this.type="delete";
		}
		this.sql=new StringBuilder(sql);
	}
	
	public Sql type(String type) {//添加操作
		this.type=type;
		return this;
	}
	
	
	public Sql params(List<Object> params) {//添加操作
		this.params.addAll(params);
		return this;
	}
	public Sql delete() {//添加操作
		this.type="delete";
		return this;
	}
	
	public Sql distinct() {//添加操作
		this.distinct=true;
		return this;
	}
	
	public Sql table(String table) {//添加操作
		this.table=table;
		return this;
	}
	
	public Sql fields(List<String> fields) {//添加操作
		this.fields=fields;
		return this;
	}
	
	public Sql add(String where) {//添加操作
		wheres.add(List.of(where));
		return this;
	}
	public Sql add(String ao,String where) {//添加操作
		wheres.add(List.of(ao,where));
		return this;
	}
	public Sql add(String name,String op,Object val) {//添加操作
		wheres.add(List.of(name,op,val));
		return this;
	}
	public Sql add(String ao,String name,String op,Object val) {//添加操作
		wheres.add(List.of(ao,name,op,val));
		return this;
	}
	public Sql add(Map<String,Object> map) {//添加操作
		map.forEach((k,v)->{
			if(k.contains(" BETWEEN")||k.contains(" between")) {
				if(v instanceof List c) {
					wheres.add(List.of(k,"",c.get(0),"and",c.get(1)));
				}else if(v!=null&&v.getClass().isArray()) {
					var c=(Object[])v;
					wheres.add(List.of(k,"",c[0],"and",c[1]));
				}
			}else if(k.contains(" ")||k.contains("=")||k.contains(">")||k.contains("<")) {
				wheres.add(List.of(k,"",v));
			}else {
				String op="=";
				if(v instanceof Collection||(v!=null&&v.getClass().isArray())) {
					op="in";
				}else if(v instanceof String s){
					if(s.contains(","))op="in";
				}
				wheres.add(List.of(k,op,v));
			}
		});
		return this;
	}
	public Sql and(String where) {//添加操作
		return add(" and ",where);
	}
	public Sql or(String where) {//添加操作
		return add(" or ",where);
	}
	public Sql and(String name,String op,Object val) {//添加操作
		return add(" and ",name,op,val);
	}
	public Sql or(String name,String op,Object val) {//添加操作
		return add(" or ",name,op,val);
	}
	
	public Sql orderBy(String orderBy) {//添加操作
		this.orderBy=orderBy;
		return this;
	}
	public Sql asc(String orderBy) {//添加操作
		this.orderBy=orderBy;
		return this;
	}
	public Sql desc(String orderBy) {//添加操作
		this.orderBy=orderBy;
		this.desc=true;
		return this;
	}
	public Sql groupBy(String groupBy) {//添加操作
		this.groupBy=groupBy;
		return this;
	}
	public int insert() {//添加操作
		type="insert";
		this.table=obj.removeStr("table");
		Object p=obj.remove("+id");
		if(p!=null){incr=true;
			if(p instanceof String pk)this.pk=pk;//更改主键名
		}
		if(incr)obj.remove(pk);//自增长不要id
		if(Lang.isEmpty(obj.get(p)))obj.remove(pk);//空id
		obj.forEach((k,v)->{
			fields.add(k);//添加字段
			params.add(v);//添加参数
		});
		return execute();
	}
	public int update() {//添加操作
		type="update";
		this.table=obj.removeStr("table");
		Object p=obj.remove("+id");
		if(p!=null&&p instanceof String pk)this.pk=pk;//更改主键名
		Object id=obj.remove(pk);
		wheres.add(List.of(pk,"=",id));
		obj.forEach((k,v)->{
			fields.add(k);//添加字段
			params.add(v);//添加参数
		});
		return execute();
	}

	public static Sql select(String sql,Class<?> c) {//添加操作
		return create(sql);
	}
	
	public static <T>T insert(T m) {//添加操作
		var s=Sql.create();
		s.obj=Json.jsonObject(m);
		s.insert();
		return (T)s.obj.toBean(m.getClass());
	}
	public static long update(Object m) {//添加操作
		var s=Sql.create();
		s.obj=Json.jsonObject(m);
		return s.update();
	}
	public static <T>T mager(T m) {//添加操作
		var s=Sql.create();
		s.obj=Json.jsonObject(m);
		Object p=s.obj.get("+id");
		if(p!=null){
			if(p instanceof String pk)s.pk=pk;//更改主键名
		}
		Object id=s.obj.get(s.pk);
		if(id!=null){
			s.update();
		}else {
			s.insert();
		}
		return m;
	}
	public static long delete(String table,Object id) {//添加操作
		var s=Sql.create();
		s.type("delete").table(table).add(s.pk,"=",id);
		return s.execute();
	}
	public static long delete(String table,Sql where) {//添加操作
		var s=Sql.create();
		s.type("delete").table(table).add(where.toString());
		s.params(where.params);
		return s.execute();
	}
	public static Sql where(String where) {
		return new Sql().add(where);
	}
	public static Sql where(Map<String,Object> where) {
		return new Sql().add(where);
	}
	public static Sql where(String name,String op,Object val) {
		return new Sql().add("where",name,op,val);
	}
	public static Sql create() {return new Sql();}
	public static Sql create(String sql) {
		return new Sql(sql);
	}
	
	public String toString() {
		if(sql==null) {
			sql=new StringBuilder();
			if("select".equalsIgnoreCase(type)) {
				sql.append(type).append(" ");
				if(distinct)sql.append(" distinct ");
				sql.append(" ").append(fields==null?"*":Strings.join(fields,","))
				.append(" from ").append(table);
			}else if("insert".equalsIgnoreCase(type)){
				sql.append(type).append(" ");
				String vs="?,".repeat(fields.size());
				sql.append("into ").append(table).append("(")
				.append(Strings.join(fields,","))
				.append(") values(").append(vs.substring(0,vs.length()-1)).append(")");
			}else if("update".equalsIgnoreCase(type)) {
				sql.append(type).append(" ");
				sql.append(table).append(" set ")
				.append(Strings.join(fields,"=?,")).append("=?");
			}else if("delete".equalsIgnoreCase(type)) {
				sql.append(type).append(" ");
				sql.append(" from ").append(table);
			}else if("drop".equalsIgnoreCase(type)){
				sql.append(type).append(" ");
				sql.append(" from ").append(table);
			}else {//其他操作
				
			}
		}
		if(wheres.size()>0) {
			append("where",wheres.get(0));
			for (int i=1;i<wheres.size();i++) {
				append("and",wheres.get(i));
			}
		}
		if(orderBy!=null) {
			sql.append(" order by ").append(orderBy);
			if(desc)sql.append(" desc ");
		}
		if(groupBy!=null) {
			sql.append(groupBy);
		}
		if(pageNumber>-1) {
			sql.append(Jdbc.paginationSQL(pageNumber, pageSize));
		}
		return sql.toString();
	}
	
	private void append(String cnd,List<Object> list) {
		if(list.size()==1) {
			String v=Casts.toString(list.get(0));
			if(v.contains("where")) {
				sql.append(" ").append(list.get(0));
			}else {
				sql.append(" ").append(cnd).append(" ").append(list.get(0));
			}
		}else if(list.size()==2) {
			sql.append(" ").append(list.get(0)).append(" ?");
			params.add(list.get(1));
		}else if(list.size()==3) {
			sql.append(" ").append(cnd).append(" ").append(list.get(0))
			.append(" ").append(list.get(1)).append(" ?");
			params.add(Jdbc.whereValue(list.get(2)));
		}else if(list.size()==4) {
			sql.append(" ").append(list.get(0)).append(" ").append(list.get(1))
			.append(" ").append(list.get(2)).append(" ?");
			params.add(Jdbc.whereValue(list.get(3)));
		}else if(list.size()==5) {//betten
			sql.append(" ").append(list.get(0)).append(" ? ")
			.append(list.get(3)).append(" ?");
			params.add(Jdbc.whereValue(list.get(2)));
			params.add(Jdbc.whereValue(list.get(4)));
		}
	}
	
	public <T> List<T> queryList(Class<T> c) {
		isQuerylist=true;
		this.c=c;
		execute();
        return (List<T>)querylist;
    }

    public <T> Set<T> querySet(Class<T> c) {
    	execute();
        return (Set<T>) querySet;
    }

    public <T> T fetch(Class<T> c) {
    	pageNumber=1;pageSize=-2;//限制一条
    	this.c=c;
    	execute();
        return (T) single;
    }

    public int execute() {
    	PreparedStatement p = null;
    	int res=0;
    	String showSql=null;
        try (var conn = Jdbc.getConnection();) {
        	if("insert".equals(type)&&incr) {
        		p = conn.prepareStatement(toString(),Statement.RETURN_GENERATED_KEYS);
        	}else {
        		p = conn.prepareStatement(toString());
        	}
        	if(pageSize==-2)p.setMaxRows(1);
            Jdbc.setParameters(p, params);
            if (Jdbc.showSql) {
            	showSql=p.toString().replace("com.mysql.cj.jdbc.ClientPreparedStatement","");
            }
            if("select".equalsIgnoreCase(type)) {//查询
            	try (ResultSet rs = p.executeQuery()) {
                    if(pageSize>-2) {
                        while (rs.next()){
                            Object item = createItemFromRs(rs);
                            if(isQuerylist) {
                            	if(querylist==null)querylist=new ArrayList<>();
                            	querylist.add(item);
                        	}else {
                        		if(querySet==null)querySet=new HashSet<>();
                        		querySet.add(item);
                        	}
                            ++res;
                        }
                    }else {
                    	if (rs.next()) {
                            single = createItemFromRs(rs);
                            res=1;
                        }
                    }
                }
            }else {
            	var r=p.execute();
            	res=p.getUpdateCount();
            	if(incr) {
            		var gs=p.getGeneratedKeys();
            		gs.next();
            		auto=gs.getLong(1);
            		if(incr)obj.put(pk,auto);//自增长加入自动增长列
            	}
            }
        } catch (SQLException e) {
            e.printStackTrace();
            res=-1;
        } finally {
			Io.close(p);
			Jdbc.log.info("[row=%d] sql{}",res,showSql==null?":Jdbc.showSql=false":showSql);
		}
        return res;
    }

    private Object createItemFromRs(ResultSet rs) throws SQLException {
        if (Jdbc.isPrimitiveSql(c)) {
            if (c == JsonObject.class || c == JsonArray.class) {
                return Json.parse(rs.getString(1));
            } else {
                return rs.getObject(1);
            }
        } else if (Map.class.isAssignableFrom(c)) {
            return new JsonObject(rs);
        } else if (Collection.class.isAssignableFrom(c)) {
            return new JsonArray(rs);
        } else if (c.isArray()) {
            return new JsonArray(rs).toArray();
        } else {
            return new JsonObject(rs).toBean(c);
        }
    }
	
	public static void main(String[] args) {
//		Sql s=new Sql();
////		s.mager(Map.of("table","xx","name","abc","age",123,"uid",22,"+id","uid"));
//		System.out.println(s);
//		System.out.println(s.params);
		System.out.println(Sql.where("a","=",1));
	}
}
